﻿using DuckDB.NET.Data;
using FastEmit;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace Dapper.Sharding
{
    internal partial class DuckDbTable<T> : ITable<T> where T : class
    {
        public DuckDbTable(string name, IDatabase database) : base(name, database, SqlFieldCacheUtils.GetDuckDbFieldEntity<T>())
        {

        }

        #region insert

        protected override string SqlInsert()
        {
            return $"INSERT INTO {Name} ({SqlField.AllFields})VALUES({SqlField.AllFieldsAt})";
        }

        protected override string SqlInsertIdentity()
        {
            return $"INSERT INTO {Name} ({SqlField.AllFields})VALUES({SqlField.AllFieldsAt})";
        }

        protected override string SqlInsert(List<string> fields)
        {
            if (fields == null)
            {
                return SqlInsert();
            }
            var list = SqlField.AllFieldList.Intersect(fields).ToList();
            var f = CommonUtil.GetFieldsStr(list, "", "");
            var fat = CommonUtil.GetFieldsAtStr(list, "$");
            return $"INSERT INTO {Name} ({f})VALUES({fat})";
        }

        protected override string SqlInsertIgnore(List<string> fields)
        {
            if (fields == null)
            {
                return SqlInsert();
            }
            var list = SqlField.AllFieldList.Except(fields).ToList();
            var f = CommonUtil.GetFieldsStr(list, "", "");
            var fat = CommonUtil.GetFieldsAtStr(list, "$");
            return $"INSERT INTO {Name} ({f})VALUES({fat})";
        }

        protected override string SqlInsertIdentity(List<string> fields)
        {
            if (fields == null)
            {
                return SqlInsertIdentity();
            }
            var list = SqlField.AllFieldList.Intersect(fields).ToList();
            var f = CommonUtil.GetFieldsStr(list, "", "");
            var fat = CommonUtil.GetFieldsAtStr(list, "$");
            return $"INSERT INTO {Name} ({f})VALUES({fat})";
        }

        protected override string SqlInsertIdentityIgnore(List<string> fields)
        {
            if (fields == null)
            {
                return SqlInsertIdentity();
            }
            var list = SqlField.AllFieldList.Except(fields).ToList();
            var f = CommonUtil.GetFieldsStr(list, "", "");
            var fat = CommonUtil.GetFieldsAtStr(list, "$");
            return $"INSERT INTO {Name} ({f})VALUES({fat})";
        }

        #endregion

        #region Bulk Insert

        public override void BulkInsert(IEnumerable<T> modelList, DistributedTransaction tran = null, int? timeout = null, int? batchSize = null)
        {
            var t = typeof(T);
            var accssor = FastEmitFactory.GetTypeAccessor(t);
            var fields = SqlField.AllFieldList;
            using var conn = (DuckDBConnection)DataBase.GetConn();
            using var appender = conn.CreateAppender(Name);
            foreach (var item in modelList)
            {
                var row = appender.CreateRow();
                foreach (var field in fields)
                {
                    var val = accssor.GetValue(item, field);
                    if (val == null)
                    {
                        row.AppendNullValue();
                    }
                    else if (val is string valStr)
                    {
                        row.AppendValue(valStr);
                    }
                    else if (val is int valInt)
                    {
                        row.AppendValue(valInt);
                    }
                    else if (val is long valLong)
                    {
                        row.AppendValue(valLong);
                    }
                    else if (val is decimal valDecimal)
                    {
                        row.AppendValue(valDecimal);
                    }
                    else if (val is DateTime valTime)
                    {
                        row.AppendValue(valTime);
                    }
                    else if (val.GetType() == typeof(DateTime?))
                    {
                        row.AppendValue((DateTime?)val);
                    }
                    else if (val is double valDouble)
                    {
                        row.AppendValue(valDouble);
                    }
                    else if (val is float valFolat)
                    {
                        row.AppendValue(valFolat);
                    }
                    else if (val is DateTimeOffset valTimeOff)
                    {
                        row.AppendValue(valTimeOff);
                    }
                    else if (val.GetType() == typeof(DateTimeOffset?))
                    {
                        row.AppendValue((DateTimeOffset?)val);
                    }
                    else if (val is bool valBool)
                    {
                        row.AppendValue(valBool);
                    }
                    else if (val is byte valByte)
                    {
                        row.AppendValue(valByte);
                    }
                    else if (val is short valShot)
                    {
                        row.AppendValue(valShot);
                    }
                    else if (val.GetType() == typeof(int?))
                    {
                        row.AppendValue((int?)val);
                    }
                    else if (val.GetType() == typeof(long?))
                    {
                        row.AppendValue((long?)val);
                    }
                    else if (val.GetType() == typeof(decimal?))
                    {
                        row.AppendValue((decimal?)val);
                    }
                    else if (val.GetType() == typeof(double?))
                    {
                        row.AppendValue((double?)val);
                    }
                    else if (val.GetType() == typeof(float?))
                    {
                        row.AppendValue((float?)val);
                    }
                    else if (val.GetType() == typeof(bool?))
                    {
                        row.AppendValue((bool?)val);
                    }
                    else if (val.GetType() == typeof(byte?))
                    {
                        row.AppendValue((byte?)val);
                    }
                    else if (val.GetType() == typeof(short?))
                    {
                        row.AppendValue((short?)val);
                    }
                    else if (val is uint valUInt)
                    {
                        row.AppendValue(valUInt);
                    }
                    else if (val is ulong valULong)
                    {
                        row.AppendValue(valULong);
                    }
                    else if (val is sbyte valSByte)
                    {
                        row.AppendValue(valSByte);
                    }
                    else if (val is ushort valUShot)
                    {
                        row.AppendValue(valUShot);
                    }
                    else if (val.GetType() == typeof(uint?))
                    {
                        row.AppendValue((uint?)val);
                    }
                    else if (val.GetType() == typeof(ulong?))
                    {
                        row.AppendValue((ulong?)val);
                    }
                    else if (val.GetType() == typeof(sbyte?))
                    {
                        row.AppendValue((sbyte?)val);
                    }
                    else if (val.GetType() == typeof(ushort?))
                    {
                        row.AppendValue((ushort?)val);
                    }
                    else if (val is Guid valGuid)
                    {
                        row.AppendValue(valGuid);
                    }
                    else if (val.GetType() == typeof(Guid?))
                    {
                        row.AppendValue((Guid?)val);
                    }
#if CORE6
                    else if (val is DateOnly valDate)
                    {
                        row.AppendValue(valDate.ToString("yyyy-MM-dd"));
                    }
                    else if (val is TimeOnly valTimeO)
                    {
                        row.AppendValue(valTimeO.ToString("HH:mm:ss"));
                    }
                    else if (val.GetType() == typeof(DateOnly?))
                    {
                        var vv = (DateOnly?)val;
                        row.AppendValue(vv.Value.ToString("yyyy-MM-dd"));
                    }
                    else if (val.GetType() == typeof(TimeOnly?))
                    {
                        var vv = (TimeOnly?)val;
                        row.AppendValue(vv.Value.ToString("HH:mm:ss"));
                    }
#endif
                    else
                    {
                        row.AppendValue(System.Text.Json.JsonSerializer.Serialize(val, ShardingFactory.JsonOptionsDb));
                    }
                }
                row.EndRow();
            }
        }

        #endregion

        #region update

        protected override string SqlUpdate(List<string> fields = null)
        {
            string updatefields;
            if (fields == null)
                updatefields = SqlField.AllFieldsAtEqExceptKey;
            else
                updatefields = CommonUtil.GetFieldsAtEqStr(fields, "", "", "$");
            return $"UPDATE {Name} SET {updatefields} WHERE {SqlField.PrimaryKey}=${SqlField.PrimaryKey}";
        }

        protected override string SqlUpdateIgnore(List<string> fields)
        {
            string updateFields = CommonUtil.GetFieldsAtEqStr(SqlField.AllFieldExceptKeyList.Except(fields), "", "", "$");
            return $"UPDATE {Name} SET {updateFields} WHERE {SqlField.PrimaryKey}=${SqlField.PrimaryKey}";
        }

        protected override string SqlUpdateByWhere(string where, List<string> fields = null)
        {
            string updatefields;
            if (fields != null)
            {
                updatefields = CommonUtil.GetFieldsAtEqStr(fields, "", "", "$");
            }
            else
            {
                updatefields = SqlField.AllFieldsAtEqExceptKey;
            }
            return $"UPDATE {Name} SET {updatefields} {where}";
        }

        protected override string SqlUpdateByWhereIgnore(string where, List<string> fields)
        {
            string updatefields = CommonUtil.GetFieldsAtEqStr(SqlField.AllFieldExceptKeyList.Except(fields), "", "", "$");
            return $"UPDATE {Name} SET {updatefields} {where}";
        }

        #endregion

        #region delete

        protected override string SqlDeleteById()
        {
            return $"DELETE FROM {Name} WHERE {SqlField.PrimaryKey}=$id";
        }

        protected override string SqlDeleteByIds()
        {
            return $"DELETE FROM {Name} WHERE {SqlField.PrimaryKey} IN $ids";
        }

        protected override string SqlDeleteByWhere(string where)
        {
            return $"DELETE FROM {Name} {where}";
        }

        protected override string SqlDeleteAll()
        {
            return $"DELETE FROM {Name}";
        }

        #endregion

        #region aggregate

        protected override string SqlExists()
        {
            return $"SELECT 1 FROM {Name} WHERE {SqlField.PrimaryKey}=$id";
        }

        protected override string SqlExists(string where)
        {
            return $"SELECT 1 FROM {Name} {where} LIMIT 1";
        }

        protected override string SqlCount(string where = null)
        {
            return $"SELECT COUNT(1) FROM {Name} {where}";
        }

        protected override string SqlMin(string field, string where = null)
        {
            return $"SELECT MIN({field}) FROM {Name} {where}";
        }

        protected override string SqlMax(string field, string where = null)
        {
            return $"SELECT MAX({field}) FROM {Name} {where}";
        }

        protected override string SqlSum(string field, string where = null)
        {
            return $"SELECT SUM({field}) FROM {Name} {where}";
        }

        protected override string SqlAvg(string field, string where = null)
        {
            return $"SELECT AVG({field}) FROM {Name} {where}";
        }

        #endregion

        #region query

        protected override string SqlGetAll(string returnFields = null, string orderby = null)
        {
            if (string.IsNullOrEmpty(returnFields))
                returnFields = SqlField.AllFields;
            return $"SELECT {returnFields} FROM {Name} {orderby.SetOrderBy(SqlField.PrimaryKey)}";
        }

        protected override string SqlGetById(string returnFields = null)
        {
            if (string.IsNullOrEmpty(returnFields))
                returnFields = SqlField.AllFields;
            return $"SELECT {returnFields} FROM {Name} WHERE {SqlField.PrimaryKey}=$id";
        }

        protected override string SqlGetByIdForUpdate(string returnFields = null)
        {
            if (string.IsNullOrEmpty(returnFields))
                returnFields = SqlField.AllFields;
            return $"SELECT {returnFields} FROM {Name} WHERE {SqlField.PrimaryKey}=$id FOR UPDATE";
        }

        protected override string SqlGetByIdForUpdateNoWait(string returnFields = null)
        {
            if (string.IsNullOrEmpty(returnFields))
                returnFields = SqlField.AllFields;
            return $"SELECT {returnFields} FROM {Name} WHERE {SqlField.PrimaryKey}=$id FOR UPDATE NOWAIT";
        }

        protected override string SqlGetByIds(string returnFields = null)
        {
            if (string.IsNullOrEmpty(returnFields))
                returnFields = SqlField.AllFields;
            return $"SELECT {returnFields} FROM {Name} WHERE {SqlField.PrimaryKey} IN $ids";
        }

        protected override string SqlGetByIdsForUpdate(string returnFields = null)
        {
            if (string.IsNullOrEmpty(returnFields))
                returnFields = SqlField.AllFields;
            return $"SELECT {returnFields} FROM {Name} WHERE {SqlField.PrimaryKey} IN $ids FOR UPDATE";
        }

        protected override string SqlGetByIdsForUpdateNoWait(string returnFields = null)
        {
            if (string.IsNullOrEmpty(returnFields))
                returnFields = SqlField.AllFields;
            return $"SELECT {returnFields} FROM {Name} WHERE {SqlField.PrimaryKey} IN $ids FOR UPDATE NOWAIT";
        }

        protected override string SqlGetByIdsWithField(string field, string returnFields = null)
        {
            if (string.IsNullOrEmpty(returnFields))
                returnFields = SqlField.AllFields;
            return $"SELECT {returnFields} FROM {Name} WHERE {field} IN $ids";
        }

        protected override string SqlGetByWhere(string where, string returnFields = null, string orderby = null, int limit = 0)
        {
            if (string.IsNullOrEmpty(returnFields))
                returnFields = SqlField.AllFields;
            string limitStr = null;
            if (limit != 0)
            {
                limitStr = " LIMIT " + limit;
            }
            return $"SELECT {returnFields} FROM {Name} {where} {orderby.SetOrderBy(SqlField.PrimaryKey)}{limitStr}";
        }

        protected override string SqlGetByWhereFirst(string where, string returnFields = null, string orderby = null)
        {
            if (string.IsNullOrEmpty(returnFields))
                returnFields = SqlField.AllFields;
            if (string.IsNullOrEmpty(orderby))
            {
                return $"SELECT {returnFields} FROM {Name} {where} LIMIT 1";
            }
            return $"SELECT {returnFields} FROM {Name} {where} {orderby.SetOrderBy(SqlField.PrimaryKey)} LIMIT 1";
        }

        protected override string SqlGetBySkipTake(int skip, int take, string where = null, string returnFields = null, string orderby = null)
        {
            if (string.IsNullOrEmpty(returnFields))
                returnFields = SqlField.AllFields;
            return $"SELECT {returnFields} FROM {Name} {where} {orderby.SetOrderBy(SqlField.PrimaryKey)} LIMIT {take} OFFSET {skip}";
        }

        protected override string SqlGetByAscFirstPage(int pageSize, string and = null, string returnFields = null)
        {
            if (string.IsNullOrEmpty(returnFields))
                returnFields = SqlField.AllFields;
            return $"SELECT {returnFields} FROM {Name} AS A WHERE 1=1 {and} ORDER BY {SqlField.PrimaryKey} LIMIT {pageSize}";
        }

        protected override string SqlGetByAscPrevPage(int pageSize, string and = null, string returnFields = null)
        {
            if (string.IsNullOrEmpty(returnFields))
                returnFields = SqlField.AllFields;
            return $"SELECT * FROM (SELECT {returnFields} FROM {Name} AS A WHERE {SqlField.PrimaryKey}<${SqlField.PrimaryKey} {and} ORDER BY {SqlField.PrimaryKey} DESC LIMIT {pageSize}) AS B ORDER BY {SqlField.PrimaryKey}";
        }

        protected override string SqlGetByAscCurrentPage(int pageSize, string and = null, string returnFields = null)
        {
            if (string.IsNullOrEmpty(returnFields))
                returnFields = SqlField.AllFields;
            return $"SELECT {returnFields} FROM {Name} AS A WHERE {SqlField.PrimaryKey}>=${SqlField.PrimaryKey} {and} ORDER BY {SqlField.PrimaryKey} LIMIT {pageSize}";
        }

        protected override string SqlGetByAscNextPage(int pageSize, string and = null, string returnFields = null)
        {
            if (string.IsNullOrEmpty(returnFields))
                returnFields = SqlField.AllFields;
            return $"SELECT {returnFields} FROM {Name} AS A WHERE {SqlField.PrimaryKey}>${SqlField.PrimaryKey} {and} ORDER BY {SqlField.PrimaryKey} LIMIT {pageSize}";
        }

        protected override string SqlGetByAscLastPage(int pageSize, string and = null, string returnFields = null)
        {
            if (string.IsNullOrEmpty(returnFields))
                returnFields = SqlField.AllFields;
            return $"SELECT * FROM (SELECT {returnFields} FROM {Name} AS A WHERE 1=1 {and} ORDER BY {SqlField.PrimaryKey} DESC LIMIT {pageSize}) AS B ORDER BY {SqlField.PrimaryKey}";
        }

        protected override string SqlGetByDescFirstPage(int pageSize, string and = null, string returnFields = null)
        {
            if (string.IsNullOrEmpty(returnFields))
                returnFields = SqlField.AllFields;
            return $"SELECT {returnFields} FROM {Name} AS A WHERE 1=1 {and} ORDER BY {SqlField.PrimaryKey} DESC LIMIT {pageSize}";
        }

        protected override string SqlGetByDescPrevPage(int pageSize, string and = null, string returnFields = null)
        {
            if (string.IsNullOrEmpty(returnFields))
                returnFields = SqlField.AllFields;
            return $"SELECT * FROM (SELECT {returnFields} FROM {Name} AS A WHERE {SqlField.PrimaryKey}>${SqlField.PrimaryKey} {and} ORDER BY {SqlField.PrimaryKey} LIMIT {pageSize}) AS B ORDER BY {SqlField.PrimaryKey} DESC";
        }

        protected override string SqlGetByDescCurrentPage(int pageSize, string and = null, string returnFields = null)
        {
            if (string.IsNullOrEmpty(returnFields))
                returnFields = SqlField.AllFields;
            return $"SELECT {returnFields} FROM {Name} AS A WHERE {SqlField.PrimaryKey}<=${SqlField.PrimaryKey} {and} ORDER BY {SqlField.PrimaryKey} DESC LIMIT {pageSize}";
        }

        protected override string SqlGetByDescNextPage(int pageSize, string and = null, string returnFields = null)
        {
            if (string.IsNullOrEmpty(returnFields))
                returnFields = SqlField.AllFields;
            return $"SELECT {returnFields} FROM {Name} AS A WHERE {SqlField.PrimaryKey}<${SqlField.PrimaryKey} {and} ORDER BY {SqlField.PrimaryKey} DESC LIMIT {pageSize}";
        }

        protected override string SqlGetByDescLastPage(int pageSize, string and = null, string returnFields = null)
        {
            if (string.IsNullOrEmpty(returnFields))
                returnFields = SqlField.AllFields;
            return $"SELECT * FROM (SELECT {returnFields} FROM {Name} AS A WHERE 1=1 {and} ORDER BY {SqlField.PrimaryKey} LIMIT {pageSize}) AS B ORDER BY {SqlField.PrimaryKey} DESC";
        }

        public override void SeqUpdate(string name = null)
        {
            if (string.IsNullOrEmpty(name))
            {
                name = Name;
            }
            DataBase.Execute($"select setval('{name.ToLower()}_id_seq',(select max({SqlField.PrimaryKey}) from {name.ToLower()}))");
        }

        public override Task SeqUpdateAsync(string name = null)
        {
            if (string.IsNullOrEmpty(name))
            {
                name = Name;
            }
            return DataBase.ExecuteAsync($"select setval('{name.ToLower()}_id_seq',(select max({SqlField.PrimaryKey}) from {name.ToLower()}))");
        }

        protected override string SqlGetByWhereForUpdate(string where, string returnFields = null, string orderby = null, int limit = 0)
        {
            if (string.IsNullOrEmpty(returnFields))
                returnFields = SqlField.AllFields;
            string limitStr = null;
            if (limit != 0)
            {
                limitStr = " LIMIT " + limit;
            }
            return $"SELECT {returnFields} FROM {Name} {where} {orderby.SetOrderBy(SqlField.PrimaryKey)}{limitStr} FOR UPDATE";
        }

        protected override string SqlGetByWhereForUpdateNoWait(string where, string returnFields = null, string orderby = null, int limit = 0)
        {
            if (string.IsNullOrEmpty(returnFields))
                returnFields = SqlField.AllFields;
            string limitStr = null;
            if (limit != 0)
            {
                limitStr = " LIMIT " + limit;
            }
            return $"SELECT {returnFields} FROM {Name} {where} {orderby.SetOrderBy(SqlField.PrimaryKey)}{limitStr} FOR UPDATE NOWAIT";
        }

        protected override string SqlGetByWhereFirstForUpdate(string where, string returnFields = null, string orderby = null)
        {
            if (string.IsNullOrEmpty(returnFields))
                returnFields = SqlField.AllFields;
            if (string.IsNullOrEmpty(orderby))
            {
                return $"SELECT {returnFields} FROM {Name} {where} LIMIT 1 FOR UPDATE";
            }
            return $"SELECT {returnFields} FROM {Name} {where} {orderby.SetOrderBy(SqlField.PrimaryKey)} LIMIT 1 FOR UPDATE";
        }

        protected override string SqlGetByWhereFirstForUpdateNoWait(string where, string returnFields = null, string orderby = null)
        {
            if (string.IsNullOrEmpty(returnFields))
                returnFields = SqlField.AllFields;
            if (string.IsNullOrEmpty(orderby))
            {
                return $"SELECT {returnFields} FROM {Name} {where} LIMIT 1 FOR UPDATE NOWAIT";
            }
            return $"SELECT {returnFields} FROM {Name} {where} {orderby.SetOrderBy(SqlField.PrimaryKey)} LIMIT 1 FOR UPDATE NOWAIT";
        }



        #endregion
    }
}
